package Datebase;

import javax.swing.table.DefaultTableCellRenderer;
import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.net.URL;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.border.EmptyBorder;
import javax.swing.GroupLayout.Alignment;
import javax.swing.text.StyledDocument;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class tscore extends JFrame {

    public static void main(String[] args) {
        System.setProperty("sun.java2d.noddraw", "true");
        new tscore();
    }
    public tscore(){
        JFrame department = new JFrame("总成绩系统");
        URL resource = tscore.class.getClassLoader().getResource("kun.png");
        Image image = new ImageIcon(resource).getImage();
        department.setIconImage(image);
        department.setSize(800, 600);
        department.setLocationRelativeTo(null);
        department.setLayout(null);

        JButton button1 = new JButton("按院系查询");

        button1.setBounds(40,20,170,50);
        button1.setFont(new Font("宋体", Font.PLAIN, 15));
        button1.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                new tdepartment();
            }
        });


        JButton button2 = new JButton("按专业查询");
        button2.setBounds(40,80,170,50);
        button2.setFont(new Font("宋体", Font.PLAIN, 15));
        button2.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                new tdept();
            }
        });

        JButton button3 = new JButton("按班级查询");
        button3.setBounds(40,140,170,50);
        button3.setFont(new Font("宋体", Font.PLAIN, 15));
        button3.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                new tclass();
            }
        });


        department.add(button1);
        department.add(button2);
        department.add(button3);
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
            String user = "HR";
            String password = "root";
            Connection conn = DriverManager.getConnection(dbURL, user, password);
            String sql = "SELECT MAX(GRADE),MIN(GRADE),CNAME,DEPARTMENT,ROUND(AVG(grade),2)\n" +
                    "FROM EXAM,COURSE,STUDENT\n" +
                    "WHERE COURSE.CNO=EXAM.CNO and EXAM.CNO=COURSE.CNO AND STUDENT.SNO=EXAM.SNO " +
                    "GROUP BY CNAME,DEPARTMENT\n"+
                    "ORDER BY DEPARTMENT";
            PreparedStatement pstm = conn.prepareStatement(sql);
            ResultSet rs = pstm.executeQuery();

            int count = 0;
            while (rs.next()) {
                count++;
            }
            rs = pstm.executeQuery();

            Object[][] data = new Object[count][5];
            String[] title = {"课程名","院系","平均成绩","最高成绩","最低成绩"};
            count = 0;
            while (rs.next()) {
                data[count][0] = (rs.getString("CNAME"));
                data[count][1] = (rs.getString("DEPARTMENT"));
                data[count][2] = (rs.getString("ROUND(AVG(grade),2)"));
                data[count][3] = (rs.getString("MAX(GRADE)"));
                data[count][4] = (rs.getString("MIN(GRADE)"));
                count++;
            }
            JTable table = new JTable(data, title);
            JScrollPane scrollPane = new JScrollPane(table);
            DefaultTableCellRenderer cr = new DefaultTableCellRenderer();
            cr.setHorizontalAlignment(SwingConstants.CENTER);
            table.setDefaultRenderer(Object.class,cr);

            table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
            table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
            table.getColumnModel().getColumn(0).setPreferredWidth(200);
            table.getColumnModel().getColumn(1).setPreferredWidth(200);
            table.getColumnModel().getColumn(2).setPreferredWidth(200);
            table.getColumnModel().getColumn(3).setPreferredWidth(200);
            table.getColumnModel().getColumn(4).setPreferredWidth(200);
            table.setRowHeight(80);
            table.setFont(new Font("微软雅黑",Font.BOLD,13));
            scrollPane.setBounds(235, 20, 550, 260);
            department.add(scrollPane);

        }catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
            JOptionPane.showMessageDialog(null, "数据源错误", "错误", JOptionPane.ERROR_MESSAGE);
        }catch (SQLException sqle) {
            sqle.printStackTrace();
            JOptionPane.showMessageDialog(null, "数据操作错误", "错误", JOptionPane.ERROR_MESSAGE);
        }
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String dbURL = "jdbc:oracle:thin:@localhost:1521:XE";
            String user = "HR";
            String password = "root";
            Connection conn = DriverManager.getConnection(dbURL, user, password);
            String sql = "SELECT CNAME,DEPARTMENT,COUNT(*)\n" +
                    "FROM EXAM,COURSE,STUDENT\n" +
                    "WHERE COURSE.CNO=EXAM.CNO and EXAM.CNO=COURSE.CNO AND STUDENT.SNO=EXAM.SNO and GRADE<60\n" +
                    "GROUP BY CNAME,DEPARTMENT\n";
            PreparedStatement pstm = conn.prepareStatement(sql);
            ResultSet rs = pstm.executeQuery();

            int count = 0;
            while (rs.next()) {
                count++;
            }
            rs = pstm.executeQuery();

            Object[][] data = new Object[count][5];
            String[] title = {"课程名","院系","不及格人数"};
            count = 0;
            while (rs.next()) {
                data[count][0] = (rs.getString("CNAME"));
                data[count][1] = (rs.getString("DEPARTMENT"));
                data[count][2] = (rs.getString("COUNT(*)"));
                count++;
            }
            JTable table = new JTable(data, title);
            JScrollPane scrollPane = new JScrollPane(table);
            DefaultTableCellRenderer cr = new DefaultTableCellRenderer();
            cr.setHorizontalAlignment(SwingConstants.CENTER);
            table.setDefaultRenderer(Object.class,cr);

            table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
            table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
            table.getColumnModel().getColumn(0).setPreferredWidth(200);
            table.getColumnModel().getColumn(1).setPreferredWidth(200);
            table.getColumnModel().getColumn(2).setPreferredWidth(200);
            table.setRowHeight(80);
            table.setFont(new Font("微软雅黑",Font.BOLD,15));
            scrollPane.setBounds(235, 290, 450, 180);
            department.add(scrollPane);

        }catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
            JOptionPane.showMessageDialog(null, "数据源错误", "错误", JOptionPane.ERROR_MESSAGE);
        }catch (SQLException sqle) {
            sqle.printStackTrace();
            JOptionPane.showMessageDialog(null, "数据操作错误", "错误", JOptionPane.ERROR_MESSAGE);
        }
        department.setVisible(true);

    }

}
